package com.gcsoftware.web.mapper;

import com.gcsoftware.authentications.beans.UserInfo;
import com.gcsoftware.web.beans.T_COM_SYS_USER;
import com.gcsoftware.web.beans.T_COM_SYS_USERPWD;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by qs on 2018/3/14.
 */
@Mapper
public interface T_COM_SYS_USERMAPPER {

    @Select("<script>SELECT usr.C_USER_ID,C_LOGIN_NAME ,case when log.D_log_time is not null then to_char(log.D_log_time,'yyyy-MM-dd hh:mi') else '' end c_LAST_LOGIN_TIME,C_USER_EMAIL ,C_STATUS,dict.C_DICT_ITEM_NAME c_USER_STATUS,to_char(usr.D_CREATE_TIME,'yyyy-MM-dd hh:mm') c_CREATE_TIME ,usr.C_COMPANY_ID ,com.C_COMPANY_NAME ,usr.C_ROLE_ID,rs.C_ROLE_NAME,usr.c_IS_DEFAULT,usr.c_USER_REMARK FROM T_COM_SYS_USER usr left join (select c_user_id, d_log_time from T_COM_SYS_LOGS where c_LOG_TYPE='LOG_TYPE_LOGIN' order by d_log_time desc limit 1 ) log on log.c_user_id = usr.C_USER_ID,T_GW_ROLES  rs ,T_GW_COMPANY com,T_COM_SYS_DICTITEM dict  WHERE rs.c_ROLE_ID = usr.c_ROLE_ID AND rs.C_ROLE_TYPE ='1' AND com.C_COMPANY_ID =usr.C_COMPANY_ID AND dict.C_DICT_ITEM_VALUE =usr.C_STATUS AND dict.C_DICT_GROUP_VALUE ='GRP_USER_STATUS' <when test='null != c_USER_NAME'> AND (usr.c_LOGIN_NAME like CONCAT(CONCAT('%',#{c_USER_NAME}),'%') or usr.c_USER_EMAIL like CONCAT(CONCAT('%',#{c_USER_NAME}),'%') )</when> <when test='null != c_STATUS'> AND c_STATUS = #{c_STATUS}</when><when test='null != c_COMPANY_ID'> AND usr.c_COMPANY_ID = #{c_COMPANY_ID}</when> ORDER BY usr.D_LAST_MODIFY DESC </script>")
    public List<T_COM_SYS_USER> queryForList(@Param(value = "c_USER_NAME") String c_USER_NAME, @Param(value = "c_STATUS") String c_STATUS,@Param(value = "c_COMPANY_ID") String c_COMPANY_ID);

    @Select("SELECT max(C_USER_ID) FROM T_COM_SYS_USER WHERE c_USER_ID not like 'SYS_ADMIN_%'")
    public String getMaxId();


    @Select("<script>SELECT C_USER_ID ,C_LOGIN_NAME ,C_USER_EMAIL ,usr.C_STATUS ,usr.C_COMPANY_ID ,C_ROLE_ID ,N_LOGIN_FAILED_COUNT,c_USER_REMARK,com.c_COMPANY_NAME FROM T_COM_SYS_USER usr,T_GW_COMPANY com WHERE com.c_COMPANY_ID = usr.c_COMPANY_ID <when test='null != c_USER_ID'> AND c_USER_ID = #{c_USER_ID}</when><when test='null != c_LOGIN_NAME'> AND c_LOGIN_NAME = #{c_LOGIN_NAME}</when><when test='null != c_USER_EMAIL'> AND c_USER_EMAIL = #{c_USER_EMAIL}</when></script>")
    public T_COM_SYS_USER get(@Param(value = "c_USER_ID") String c_USER_ID,
                              @Param(value = "c_LOGIN_NAME") String c_LOGIN_NAME,
                              @Param(value = "c_USER_EMAIL") String c_USER_EMAIL
    );

    @Insert("INSERT INTO T_COM_SYS_USER (C_USER_ID ,C_LOGIN_NAME ,C_USER_EMAIL,C_COMPANY_ID,C_ROLE_ID,c_STATUS) VALUES (#{c_USER_ID},#{c_LOGIN_NAME},#{c_USER_EMAIL},#{c_COMPANY_ID},#{c_ROLE_ID},#{c_STATUS})")
    public int insert(T_COM_SYS_USER user);

    @Update("UPDATE T_COM_SYS_USER SET D_LAST_MODIFY=CURRENT_TIMESTAMP() , C_USER_EMAIL= #{c_USER_EMAIL},C_COMPANY_ID = #{c_COMPANY_ID},C_ROLE_ID = #{c_ROLE_ID},c_USER_REMARK=#{c_USER_REMARK} WHERE C_USER_ID = #{c_USER_ID}")
    public int update(T_COM_SYS_USER user);

    @Update("UPDATE T_COM_SYS_USER SET D_LAST_MODIFY=CURRENT_TIMESTAMP() , c_STATUS = #{c_STATUS} where c_USER_ID = #{c_USER_ID} ")
    public int updateUserStatus(@Param(value = "c_USER_ID")String c_USER_ID, @Param(value = "c_STATUS") String c_STATUS);

    @Delete("DELETE FROM T_COM_SYS_USER WHERE c_USER_ID = #{c_USER_ID}")
    public int delete(@Param(value = "c_USER_ID") String c_USER_ID);

    @Select("SELECT C_PASS_ID ,C_USER_ID ,C_PASSWORD ,C_SALT ,c_STATUS ,C_PWD_QUESTION ,C_PWD_ANSWER  FROM T_COM_SYS_USERPWD WHERE C_PASS_ID = #{C_PASS_ID} and c_STATUS = 'PWD_STATUS_NORMAL'")
    public T_COM_SYS_USERPWD getUserPwd(@Param(value = "C_PASS_ID") String C_PASS_ID);

    @Insert("INSERT INTO T_COM_SYS_USERPWD(C_PASS_ID ,C_USER_ID ,C_PASSWORD ,C_SALT ,D_CREATE_TIME ,c_STATUS ,C_PWD_QUESTION ,C_PWD_ANSWER) VALUES(#{c_PASS_ID},#{c_USER_ID},#{c_PASSWORD},#{c_SALT},CURRENT_TIMESTAMP(),#{c_STATUS},#{c_PWD_QUESTION},#{c_PWD_ANSWER})")
    public int insertPwd(T_COM_SYS_USERPWD pwd);

    @Update("UPDATE T_COM_SYS_USERPWD SET C_STATUS = #{c_STATUS} ,D_EXPIRE_TIME = CURRENT_TIMESTAMP() WHERE c_USER_ID = #{c_USER_ID}")
    public int updatePwdStatus(@Param(value = "c_USER_ID") String c_USER_ID,@Param(value = "c_STATUS") String c_STATUS);

    @Select("SELECT max(C_PASS_ID) FROM T_COM_SYS_USERPWD WHERE C_PASS_ID not like 'DFT_USER_PWD_%'")
    public String getMaxPWDId();

    @Delete("DELETE T_COM_SYS_USERPWD WHERE C_USER_ID = #{c_USER_ID}")
    public int deletePwd(@Param(value = "c_USER_ID") String c_USER_ID);

    @Select("SELECT usr.c_USER_ID userid,usr.c_COMPANY_ID companyId,usr.C_STATUS userstatus,c_login_name username,c_user_email useremail,pwd.C_PASSWORD  userpwd,C_SALT salt FROM T_COM_SYS_USER usr ,T_COM_SYS_USERPWD pwd where usr.c_USER_ID = pwd.C_USER_ID and pwd.C_STATUS ='PWD_STATUS_NORMAL' and usr.c_STATUS='USER_STATUS_NORMAL' and usr.C_LOGIN_NAME =#{loginName} ")
    public UserInfo getUserInfo(String loginName) ;
}
